package cn.neusoft.dao.impl;

import cn.neusoft.bean.Food;
import cn.neusoft.dao.IFoodDao;
import cn.neusoft.query.BaseQuery;
import cn.neusoft.query.FoodQuery;
import cn.neusoft.utils.JdbcUtils;
import com.alibaba.druid.util.StringUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class FoodDao implements IFoodDao {

    private QueryRunner queryRunner;

    public FoodDao(){
        queryRunner = JdbcUtils.getQueryRunner();
    }

    @Override
    public int add(Food food) throws SQLException {
        String sql = "insert into t_food(foodName, foodExplain, foodImg, foodPrice, businessId, remarks)  values(?,?,?,?,?,?)";
        return queryRunner.update(sql,food.getFoodName(),
                food.getFoodExplain(),
                food.getFoodImg(),
                food.getFoodPrice(),
                food.getBusinessId(),
                food.getRemarks());
    }

    @Override
    public int update(Food food) throws SQLException {
        String sql = "update t_food set foodName=?, foodExplain=?, foodImg=?, foodPrice=?, businessId=?, remarks=? where foodId=?";
        return queryRunner.update(sql,
                food.getFoodName(),
                food.getFoodExplain(),
                food.getFoodImg(),
                food.getFoodPrice(),
                food.getBusinessId(),
                food.getRemarks(),
                food.getFoodId());
    }

    @Override
    public int delete(Integer id) throws SQLException {
        String sql = "delete from t_food where foodId=?";
        return queryRunner.update(sql,id);
    }

    @Override
    public Food getById(Integer id) throws SQLException {
        String sql = "select foodId, foodName, foodExplain, foodImg, foodPrice, businessId, remarks from t_food where foodId=?";
        return queryRunner.query(sql,new BeanHandler<Food>(Food.class),id);
    }

    @Override
    public List<Food> getAll() throws SQLException {
        String sql = "select foodId, foodName, foodExplain, foodImg, foodPrice, businessId, remarks from t_food";
        return queryRunner.query(sql,new BeanListHandler<Food>(Food.class));
    }

    @Override
    public int getCount(BaseQuery baseQuery) throws SQLException {
        //先将BaseQuery转成真实类型 FoodQuery
        FoodQuery foodQuery = (FoodQuery)baseQuery;
        //准备参数集合
        List<Object> params = new ArrayList<>();

        String sql = "select count(foodId) from t_food where 1=1 ";

            if(!StringUtils.isEmpty(foodQuery.getFoodName())){
                sql += " and foodName like concat('%',?,'%')";
                params.add(foodQuery.getFoodName());
            }
        System.out.println(sql);
        System.out.println(params);

        return queryRunner.query(sql,new ScalarHandler<Long>(),params.toArray()).intValue();
    }

    @Override
    public List<Food> getByPage(BaseQuery baseQuery) throws SQLException {

        //先将BaseQuery转成真实类型 FoodQuery
        FoodQuery foodQuery = (FoodQuery)baseQuery;
        //准备参数集合
        List<Object> params = new ArrayList<>();

        String sql = "select foodId, foodName, foodExplain, foodImg, foodPrice, businessId, remarks from t_food where 1=1 ";

               if(!StringUtils.isEmpty(foodQuery.getFoodName())){
                   sql += " and foodName like concat('%',?,'%')";
                   params.add(foodQuery.getFoodName());
               }

               sql += "order by foodId desc limit ?,?";

        params.add(foodQuery.getStartIndex());
        params.add(foodQuery.getPageCount());

        System.out.println(sql);
        System.out.println(params);

        return queryRunner.query(sql,new BeanListHandler<Food>(Food.class),params.toArray());
    }
}
